package com.xiguaji.tiktok.v1.ins.brand.repository;

import com.xiguaji.tiktok.v1.ins.brand.dto.*;
import com.xiguaji.tiktok.v1.ins.brand.model.CommonBrand;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface CommonBrandRepository extends CrudRepository<CommonBrand,Integer>,CommonBrandExtRepository {


    @Query("SELECT m.uid FROM ins_rel_brand_blogger b left join  ins_blogger_main m on b.uid = m.uid where bid = :brandId")
    List<Long> findUidByBrandId(int brandId);

    @Query("SELECT GROUP_CONCAT(CONCAT('\\'',m.username,'\\'')) FROM ins_rel_brand_blogger b left join  ins_blogger_main m on b.uid = m.uid where bid = :brandId")
    String findUsernameByBrandId(int brandId);

    @Query("select count(*) posts,IFNULL(SUM(im.video_view_count),0) views,IFNULL(SUM(im.like_count),0) likes,IFNULL(SUM(im.comment_count),0) reviews,IFNULL(SUM(im.like_count+im.comment_count),0) engagements " +
            "from  ins_rel_brand_media rbm "+
            "inner join  ins_media im on rbm.shortcode = im.shortcode where rbm.bid = :bid and rbm.taken_time between :startTime and :endTime")
    MediaCountResp findBrandMediaCount(int bid,long startTime,long endTime);


    @Query("select count(*) posts, IFNULL(SUM(im.video_view_count),0) views,IFNULL(SUM(im.like_count),0) likes,IFNULL(SUM(im.comment_count),0) reviews,IFNULL(SUM(im.like_count+im.comment_count),0) engagements ,FROM_UNIXTIME(im.taken_time+(3600*13),'%m-%d') date_time " +
            "from  ins_rel_brand_media rbm "+
            "inner join  ins_media im on rbm.shortcode = im.shortcode where rbm.bid = :bid and rbm.taken_time between :startTime and :endTime GROUP BY date_time order by date_time ")
    List<MediaCountResp> findBrandMediaCountGroupDay(int bid, long startTime, long endTime);


    @Query("select count(DISTINCT(p.product_id)) products,count(*) posts,IFNULL(SUM(m.video_view_count),0) views," +
            "IFNULL(SUM(m.like_count),0) likes,IFNULL(SUM(m.comment_count),0) reviews,IFNULL(SUM(m.like_count+m.comment_count),0) engagements " +
            "from ins_rel_media_product mp INNER JOIN ins_product p ON mp.product_id = p.product_id " +
            "left join ins_media m ON mp.shortcode = m.shortcode " +
            "where p.merchant_uid in (:uIds) " +
            "and mp.taken_time between :startTime and :endTime")
    MediaCountResp findBrandMediaCountByProduct(List<Long> uIds,long startTime,long endTime);


    @Query("select count(*) posts,IFNULL(SUM(m.video_view_count),0) views,IFNULL(SUM(m.like_count),0) likes,IFNULL(SUM(m.comment_count),0) reviews,IFNULL(SUM(m.like_count+m.comment_count),0) engagements,FROM_UNIXTIME(mp.taken_time+(3600*13),'%m-%d') date_time " +
            "from ins_rel_media_product mp " +
            "INNER JOIN ins_product p ON mp.product_id = p.product_id " +
            "left join ins_media m ON mp.shortcode = m.shortcode " +
            "where p.merchant_uid in (:uIds) " +
            "and mp.taken_time between :startTime and :endTime " +
            "GROUP BY date_time ORDER BY date_time")
    List<MediaCountResp> findBrandProductMediaCountByGroupDay(List<Long> uIds,long startTime,long endTime);


    @Query("select count(distinct(p.product_id)) count,p.overall_category_name category " +
            "from ins_rel_media_product mp " +
            "INNER JOIN ins_product p ON mp.product_id = p.product_id " +
            "where p.merchant_uid in (:uIds) " +
            "and mp.taken_time between :startTime and :endTime GROUP BY category order by count desc")
    List<ProductCategoryCount> findBrandMediaCountByGroupProductCategory(List<Long> uIds, long startTime, long endTime);



    @Query("select count(distinct(rbm.owner_uid)) ad_partner,count(*) posts,IFNULL(SUM(rbm.video_view_count),0) views,IFNULL(SUM(rbm.like_count),0) likes,IFNULL(SUM(rbm.comment_count),0) reviews,IFNULL(SUM(rbm.like_count+rbm.comment_count),0) engagements " +
            "from  ins_rel_brand_media rbm "+
            "where rbm.bid = :bid and rbm.is_brand_account = :isBrandAccount and rbm.taken_time between :startTime and :endTime")
    MediaCountResp findBrandInfluencerCount(int bid,boolean isBrandAccount,long startTime,long endTime);

    @Query("select count(DISTINCT(rbm.owner_uid)) partner_count,FROM_UNIXTIME(rbm.taken_time+(3600*13),'%w')+1 week_day " +
            "from ins_rel_brand_media rbm where rbm.bid = :bid and is_brand_account = 0 and rbm.taken_time between :startTime and :endTime GROUP BY week_day ORDER BY week_day")
    List<PartnerWeekDayCountResp> findBrandAdBloggerOfWeekDay(int bid, long startTime, long endTime);


    @Query("select last_st_time from ins_st_brand where bid = :brandId")
    Long lastStTime(int brandId);

    @Query("select * from common_brand where en_name = :brandName limit 0,1")
    CommonBrand findByEnName(String brandName);

    /**
     * 获取品牌账户帖子统计信息
     * @param bid
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("SELECT count(1) total, sum(like_count) like_count, sum(comment_count) comment_count, sum(video_view_count)view_count, " +
            " sum(like_count + comment_count) ec " +
            " FROM ins_media " +
            " WHERE owner_uid IN ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = :bid ) " +
            " AND taken_time BETWEEN :startTime AND :endTime")
    InsBrandAccountOverviewResp findBrandAccountStMedia(int bid,long startTime,long endTime);

    /**
     * 品牌账户商业分类
     * @param bid
     * @return
     */
    @Query("select commercial_category_name category,count(1) val from ins_blogger_main " +
            " where uid in ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = :bid ) and commercial_category_name != '' " +
            " group by commercial_category_name")
    List<InsBrandAccountCategory> findBrandAccountIndustry(int bid);

    /**
     * 品牌账户商业分类
     * @param bid
     * @return
     */
    @Query("select t1.size_name,ifnull(t2.total,0)total,ifnull(t2.follower_count,0)follower_count from " +
            "(select id,size_name from ins_dic_follower_size where id < 701)t1 " +
            "left join " +
            "(select follower_size,count(1)total,sum(follower_count)follower_count from ins_blogger_main where uid in ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = :bid ) " +
            "group by follower_size)t2 " +
            "on t1.id = t2.follower_size " +
            "order by id desc")
    List<InsBrandAccountFollowerSize> findBrandAccountFollowerSize(int bid);

    /**
     * 品牌账户列表
     * @param bid
     * @param startTime
     * @param endTime
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Query(" select t1.*,t2.post,t2.ec from " +
            " (select uid,follower_count,username,commercial_category_name category,pic_url " +
            " from ins_blogger_main where uid in ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = :bid ))t1 " +
            " left join " +
            " (SELECT " +
            "  owner_uid, " +
            " count(1) post, " +
            " sum(like_count + comment_count) ec " +
            " FROM " +
            " ins_media  " +
            " WHERE " +
            " owner_uid IN ( SELECT uid FROM ins_rel_brand_blogger WHERE bid = :bid )  " +
            " AND taken_time BETWEEN :startTime AND :endTime " +
            " group by owner_uid)t2 " +
            " on t2.owner_uid = t1.uid " +
            " order by ec desc, post desc ,follower_count desc " +
            " limit :startIndex,:pageSize")
    List<InsBrandAccountInfo> findBrandAccountList(int bid,long startTime,long endTime,int startIndex,int pageSize);

    @Query("select b.pic_url from ins_rel_brand_blogger bb left join ins_blogger_main b on bb.uid = b.uid where bb.bid = :bid order by b.follower_count desc limit 0,1")
    String findCommonBrandMoreFansBloggerPic(int bid);
}
